package Dauphin;
use strict;
use warnings;
use CGI;
use HTML::Template;
use DBI;
##use Data::Dumper;    ## not needed in production code
my $VERSION = '1.01';
our ($config);    # the username, password etc for the db
my (
    $d,           # database handle
    $c,           # cgi object
    $t,           # html::template object
    $mode         # what mode we're in
);
my @required_fields = qw(
  main_table 	 key_field
  display_fields config_file
  search_fields
);
print "Content-type: text/html\n\n";

sub new {
    my $class = shift();
    ############################################################################
    #    Load all params from the hash sent by the new() call                  #
    ############################################################################
    my %params       = @_;
    my @param_errors = ();
    ############################################################################
    #    Loop through reqired and die if there's a required one missing,       #
    #    otherwise, set each one                                               #
    ############################################################################
    for (@required_fields) {
        push(
            @param_errors,
            format_error(
                "'$_' has not been specified in new(). It is a required field.",
                __PACKAGE__,
                __LINE__
            )
          ),

          unless $params{$_};
    }
    if (@param_errors) { report_error(@param_errors) }
    my $self;
    for ( keys(%params) ) {
        $self->{$_} = $params{$_};
    }
    $self->{'template_path'} = './templates/'
      unless defined $self->{'template_path'};
    $self->{'css_path'} = './css/' unless defined $self->{'css_path'};
    $self->{'sort_field'} = $self->{'display_fields'}->[0]
      unless defined $self->{'sort_field'};
    ############################################################################
    #    Require the config file which has the database info, username and     #
    #    password                                                              #
    ############################################################################
    eval { require $self->{'config_file'} };
    if ($@) {
        report_error(
            format_error(
"Couldn't open '$self->{'config_file'}' the database config file: $!",
                __PACKAGE__,
                __LINE__
            )
        );
    }
    ############################################################################
    #    Set up CGI object                                                     #
    ############################################################################
    $c = CGI->new();
    ############################################################################
    #    Set up DBI object                                                     #
    ############################################################################
    $d = DBI->connect( "DBI:mysql:database=$config->{'db'}",
        $config->{'username'}, $config->{'password'} )
      || report_error(
        format_error(
            "Couldn't connect to the '$config->{'db'}' database: <em>"
              . DBI->errstr() . '</em>',
            __PACKAGE__,
            __LINE__
        )
      );
    ############################################################################
    #    Set the mode                                                          #
    ############################################################################
    $mode = $c->param('mode');
    return bless $self, $class;
}
###########################################################################
#  The run() subroutine does everything. There should only be "new",      #
#  then "run" in the script which calls Dauphin.                          #
###########################################################################
sub run {
    my $self = shift();

    ########################################################################
    #  The default mode is "list" where you see a list of records in       #
    #  the database.                                                       #
    ########################################################################
    my $mode = $c->param('mode') || 'list';
    ########################################################################
    #  Edit mode for editing a record.                                     #
    ########################################################################
    if ( $mode eq 'edit' ) {
        $self->initialise_fields();
        $self->populate_fields( $c->param('id') );
        $self->output_form();
    }
    if ( $mode eq 'search' ) {
        $self->search_database();
        ##$self->output_search_results();
        $self->output_list();
    }
    ########################################################################
    #  New mode creates an empty record.                                   #
    ########################################################################
    if ( $mode eq 'new' ) {
        $self->initialise_fields();
        $self->output_form();
    }
    ########################################################################
    #  List mode outputs a list with or without default parameters         #
    #  like starting from which record, etc.                               #
    ########################################################################
    if ( $mode eq 'list' ) {
        $self->output_list();
    }
    if ( $mode eq 'display' ) {
        $self->initialise_fields();
        $self->display();
    }
    ########################################################################
    #  Delete mode has two options, because it asks for confirmation       #
    #  then only deletes when the confirmation is given.                   #
    ########################################################################
    if ( $mode eq 'delete' ) {
        if ( $c->param('confirm_delete') eq 'yes' ) {
            $self->delete_record( $c->param('id') );
        }
        elsif ( $c->param('confirm_delete') eq 'no' ) {
            $self->output_list(
                message => 'delete cancelled',
                offset  => $c->param('offset')
            );
        }
        else {
            $self->initialise_fields();
            $self->display();
        }
    }
    ########################################################################
    #  Save mode, with two options, it's either a new one being saved      #
    #  or an old one.                                                      #
    ########################################################################
    if ( $mode eq 'save' ) {
        $self->initialise_fields();
        #####################################################################
        #  This block is for things which already have an ID, that is,      #
        #  existing records which have been edited.                         #
        #####################################################################
        if ( $c->param('id') ) {
            $self->update_record();
        }
        #####################################################################
        #  End of "if it already has an ID", i.e. if it's an old record     #
        #  being edited and updated.                                        #
        #####################################################################
        else
          #####################################################################
          #  If it doesn't have an ID and is therefore a brand new record     #
          #  to be inserted                                                   #
          #####################################################################
        {
            $self->insert_record();
        }
    }
}

sub search_database {
    ########################################################################
    #  Search database.                                                    #
    ########################################################################
    my $self = shift();

    #my @search_fields  = map { "`$_`" } @{ $self->{'search_fields'} };
    my @sorted_results = ();
    ############################################################################
    #    split search string on one or more whitespaces                        #
    ############################################################################
    my @search_words =
      split( '\s+', $c->param('string') );
    my %results = ();
    foreach my $word (@search_words) {

        my $search_substring = join( ' OR ',
            map { " `$_` LIKE '%$word%' " } @{ $self->{'search_fields'} } );
        #######################################################################
        #    Do the SQL                                                       #
        #######################################################################
        my $sql_string =
          sprintf( "SELECT `$self->{key_field}` FROM `%s` WHERE %s",
            $self->{main_table}, $search_substring );

        my $sth = $d->prepare($sql_string)
          || report_error(
            format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
        $sth->execute()
          || report_error(
            format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );

        while ( my $ref = $sth->fetchrow_hashref() ) {

            ##printf( "<pre>REF: %s</pre>", Dumper($ref) );

            $results{ $ref->{ $self->{key_field} } }->{number}++;
        }
    }

############################################################################
   #    Put results, sorted by numbers, into array                             #
############################################################################

    @sorted_results = (
        sort( { $results{$b}->{number} <=> $results{$a}->{number} }
            keys(%results) )
    );
    $self->{search_results} = \@sorted_results;

}

sub delete_record {
    ########################################################################
    #  What happens when you've got the delete confirmation.               #
    ########################################################################
    my $self       = shift();
    my $id         = shift();
    my $sql_string = sprintf( "delete from `%s` where %s = ? limit 1 ",
        $self->{main_table}, $self->{key_field} );
    my $sth = $d->prepare($sql_string)
      || report_error( format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
    $sth->execute($id)
      || report_error( format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );

    foreach my $rel ( @{ $self->{links} } ) {
        my $delete_string = sprintf(
            "DELETE FROM `%s` WHERE `%s`  = ?",
            $rel->{joining_table}->{name},
            $rel->{joining_table}->{left_column}
        );
        my $sth = $d->prepare($delete_string)
          || report_error(
            format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
        $sth->execute( $c->param('id') )
          || report_error(
            format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
    }
    $self->{mode} = 'list';
    $self->output_list(
        message => 'Item deleted',
        offset  => $c->param('offset')
    );
}

sub display {
    ########################################################################
    #  What happens before you've got the delete confirmation.             #
    ########################################################################
    my $self = shift();
    $self->populate_fields( $c->param('id') );
    $t = HTML::Template->new(
        filename          => $self->{'template_path'} . 'display_template.html',
        die_on_bad_params => 0,
        loop_context_vars => 1
    );
    $t->param(
        css_path   => $self->{'css_path'},
        internal   => $self->{'fields'},
        mode       => $mode,
        id         => $self->{'id'},
        title      => $self->{'title'},
        table_name => $self->{'main_table'},
        offset     => $c->param('offset')
    );
    if ( $mode eq 'delete' ) {
        $t->param( delete_mode => 1 );
    }
    if ( $c->param('string') ) {
        $t->param( string => $c->param('string') );
    }
    if ( $self->{'external_fields'} ) {
        $t->param( external => $self->{'external_fields'} );
    }

    print $t->output();
}

sub output_list {
    my $self    = shift();
    my %args    = @_;
    my $message = $args{'message'};
    if ( $args{'string'} ) {
        $c->param( 'string' => $args{'string'} );
        $mode = 'search';
        $self->search_database();
    }
    ############################################################################
    #    offset is either sent as a param, sent via                        #
    #    the query string, or one                                              #
    ############################################################################
    my $offset = $args{'offset'}
      || $c->param('offset')
      || 1;
    ############################################################################
    #    Grab the total number of records first to calculate from.             #
    ############################################################################
    my $total;
    if ( $mode eq 'search' ) {
        $total = scalar( @{ $self->{search_results} } );
        if ( @{ $self->{search_results} } > 0 ) {
            $message = sprintf(
                "%d match%s found for &quot;%s&quot;",
                $total, ( $total > 1 ? 'es' : '' ),
                $c->param('string')
            );
        }
        else {
            $message = sprintf( "no matches found for &quot;%s&quot;",
                $c->param('string') );
        }
    }
    else {

        my $sth =
          $d->prepare(
            sprintf( "select count(*) from `%s`", $self->{main_table} ) )
          || report_error(
            format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
        $sth->execute()
          || report_error(
            format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
        ($total) = $sth->fetchrow_array;
    }

    ############################################################################
    #    Set per_page via the new() params if you need to                      #
    ############################################################################
    my $per_page = $self->{'per_page'} || 10;
    my $last_page;
    if ( ( $total % $per_page ) == 0 ) {
        $last_page = ( $total - $per_page + 1 );
    }
    else {
        $last_page = ( $total - ( $total % $per_page ) + 1 );
    }
    my $next_page     = ( $offset + $per_page );
    my $previous_page = ( $offset - $per_page );
    ############################################################################
    #    Get number of fields to be displayed and set appropriate colspan      #
    #    plus headers.                                                         #
    ############################################################################
    if ( $c->param('sort_field') ) {
        $self->{sort_field} = $c->param('sort_field');
    }

    my @display_field_names = ();
    for ( @{ $self->{display_fields} } ) {
        if ( $_ eq $self->{sort_field} ) {
            push( @display_field_names, { name => $_, sort_field_flag => 1 } );
        }
        else {
            push( @display_field_names, { name => $_ } );
        }

    }

    #    my @display_field_names =
    #     map { { name => $_ } } @{ $self->{display_fields} };
    ############################################################################
    #    All the necessary variables are set now.                              #
    ############################################################################
    my @items  = ();
    my $number = $offset;
    my $order_by;
    if ( $c->param('sort_direction') ) {
        $self->{sort_direction} = $c->param('sort_direction');
    }
    else {
        $self->{sort_direction} = 'asc';
    }
    $self->{opposite_sort_direction} =
      $self->{sort_direction} eq 'asc' ? 'desc' : 'asc';
    my $sql_string;
    if ( $mode eq 'search' && @{ $self->{search_results} } > 0 ) {
        my $id_list = join( ',', @{ $self->{search_results} } );
        $sql_string =
          sprintf(
"SELECT * from `%s` where `%s` in(%s) order by field(`%s`, %s) limit %s,%s",
            $self->{main_table}, $self->{'key_field'}, $id_list,
            $self->{'key_field'}, $id_list, ( $offset - 1 ), $per_page );

    }
    else {

        $sql_string = sprintf(
            "select * from  `%s` order  by `%s` %s limit %s,%s",
            $self->{main_table}, $self->{sort_field},
            $self->{sort_direction},
            ( $offset - 1 ), $per_page
        );
        ##print "SQL: $sql_string";
    }

    unless ( $mode eq 'search' && @{ $self->{search_results} } == 0 ) {

        my $sth = $d->prepare($sql_string)
          || report_error(
            format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
        $sth->execute()
          || report_error(
            format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );

        while ( my $ref = $sth->fetchrow_hashref() ) {
            $ref->{key_field} = $ref->{ $self->{'key_field'} };
            $ref->{number}    = $number;
            $ref->{offset}    = $offset;
            my @display_fields = ();
            foreach ( @{ $self->{'display_fields'} } ) {

                if ( $self->{lookups}->{$_} and defined( $ref->{$_} ) ) {

                    push( @display_fields,
                        { value => $self->get_lookup_value( $_, $ref->{$_} ) }
                    );

                }
                else {
                    push( @display_fields, { value => $ref->{$_} } );
                }
            }
            $ref->{display_fields} = \@display_fields;
            if ( $self->{'sort_field'} ) {
                $ref->{sort_field} = $ref->{ $self->{'sort_field'} };
            }

            if ( $self->{'max_display_length'} ) {
                foreach  ( @{$ref->{'display_fields'}} ) {
                    if ( length( $_->{value} ) > $self->{'max_display_length'} )
                    {

                        $_->{value} =
                        substr( $_->{value}, 0, $self->{'max_display_length'} ) . ' ...';

                    }
                }
            }

##print Dumper($ref);

            push( @items, $ref );
            $number++;
        }
    }
    while ( ( @items % $per_page ) > 0 ) {
        my @empty_array = ();
        $empty_array[ $#{ $self->{'display_fields'} } ] = undef;
        push( @items, { display_fields => \@empty_array } );
    }

    $t = HTML::Template->new(
        filename          => $self->{'template_path'} . 'list_template.html',
        die_on_bad_params => 0,
        loop_context_vars => 1,
        global_vars       => 1
    );

    $t->param(
        display_field_names => \@display_field_names,
        outercolspan        => ( @display_field_names + 2 ),
        items               => \@items,
        mode                => $mode,
        message             => $message,
        table_name          => $self->{'main_table'},
        offset              => $offset,
        css_path            => $self->{'css_path'},
        sort_field          => $self->{'sort_field'},
        opposite            => $self->{'opposite_sort_direction'},
    );
    if ( $mode ne 'search' ) {
        if (   $c->param('mode') eq 'save'
            || $c->param('mode') eq 'edit'
            || $c->param('mode') eq 'delete' )
        {
            $t->param( mode => 'list' );
        }
    }
    ############################################################################
    #    Set all those extra options like "next" isn't there if we're on       #
    #    last, and so on.                                                      #
    ############################################################################

    if ( $c->param('string') ) {
        $t->param( string => $c->param('string') );
    }
    unless ( $previous_page < 1 ) {
        $t->param( previous_page => $previous_page );
    }
    unless ( $offset >= $last_page ) {
        $t->param(
            last_page => $last_page,
            next_page => $next_page
        );
    }
    print $t->output();
}

sub output_form {
    ############################################################################
    #    Displays the form. With contents if populate_fields has been          #
    #    called, without if it hasn't.                                         #
    ############################################################################
    my $self = shift();

    $t = HTML::Template->new(
        filename          => $self->{'template_path'} . 'edit_template.html',
        die_on_bad_params => 0,
        loop_context_vars => 1
    );
    $t->param(
        css_path   => $self->{'css_path'},
        internal   => $self->{'fields'},
        mode       => $mode,
        id         => $self->{'id'},
        title      => $self->{'title'},
        table_name => $self->{'main_table'},
        offset     => $c->param('offset') || 1,
    );
    if ( $self->{'external_fields'} ) {
        $t->param( external_fields => $self->{'external_fields'} );
    }
    if ( $c->param('string') ) {
        $t->param( string => $c->param('string') );
    }
    print $t->output();
}

sub populate_fields {
    ############################################################################
    #    Fills the fields in the data structure with the database contents.    #
    ############################################################################
    my $self = shift();
    my $id   = shift();
    ##my $count = 0;
    if ( $self->{links} ) {
        for ( my $i = 0 ; $i < @{ $self->{links} } ; $i++ ) {
            my $rel            = @{ $self->{links} }[$i];
            my %checked_fields = ();
            my @label_fields  =
              map { "`$rel->{external_table}->{name}`.`$_`" }
              @{ $rel->{external_table}->{label_fields} };


            # I don't know why but I'd rather do this than a join:
            my $values_sql_string = sprintf(
"SELECT %s FROM `%s`, `%s`, `%s`  WHERE `%s`.`%s` = `%s`.`%s` AND `%s`.`%s` = `%s`.`%s` AND `%s`.`%s` = ?",
                join( ', ', @label_fields,  ),
                $self->{main_table},
                $rel->{external_table}->{name},
                $rel->{joining_table}->{name},
                $self->{main_table},
                $self->{key_field},
                $rel->{joining_table}->{name},
                $rel->{joining_table}->{left_column},
                $rel->{external_table}->{name},
                $rel->{external_table}->{key_field},
                $rel->{joining_table}->{name},
                $rel->{joining_table}->{right_column},
                $self->{main_table},
                $self->{key_field}
            );
            my $sth = $d->prepare($values_sql_string)
              || report_error(
                format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
            $sth->execute($id)
              || report_error(
                format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
            while ( my ($value) = $sth->fetchrow_array() ) {
                $checked_fields{$value} = 1;
            }

            foreach ( @{ $self->{'external_fields'}->[$i]->{'values'} } ) {
                if ( defined( $checked_fields{ $_->{'value'} } ) ) {
                    $_->{checked} = 1;    # check the value if it's selected
                }
            }
            if ( $mode eq 'display' || $mode eq 'delete' ) {

                # remove all the unchecked values
                @{ $self->{'external_fields'}->[$i]->{'values'} } =
                  grep { $_->{checked} }
                  @{ $self->{'external_fields'}->[$i]->{'values'} }

            }
        }
    }

    my $sql_string = sprintf( "select * from `%s` where `%s` = ?",
        $self->{main_table}, $self->{key_field} );
    my $sth = $d->prepare($sql_string)
      || report_error( format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
    $sth->execute($id)
      || report_error( format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
    my $ref = $sth->fetchrow_arrayref();
    ############################################################################
    #    Loop through the fields as an array                                   #
    ############################################################################

    for ( my $i = 0 ; $i < @{$ref} ; $i++ ) {
        ##printf("<pre>%d -- %s</pre>",$i,$ref->[$i]);
        my $field        = $self->{'fields'}->[$i];
        my %field_values = ();
        if ( $field->{'title_field'} ) {
            $self->{title} = $ref->[$i];
        }
        if ( $field->{'key_field'} ) {
            $self->{'id'} = $ref->[$i];
        }
        else {
            ## not the key field
            if ( $field->{'lookup'} ) {
                ## if it's a lookup get the lookup-table value instead of the value
                ## make this a sub, as in, getlookup(lookupname,value)

                ##printf("<pre>%s</pre>",$ref->[$i]);

                my $lookup = $self->{'lookups'}->{ $field->{'field_name'} };
                my $lookup_string = '';
                if ( ref( $lookup->{'label'} ) eq 'ARRAY' ) {
                    $lookup_string =
                      join( ',', map { "`$_`" } @{ $lookup->{'label'} } );
                }
                else {
                    $lookup_string = "`$lookup->{'label'}`";
                }
                my $sql_string = sprintf(
                    "SELECT %s from `%s` where `%s` = %s",
                    $lookup_string,   $lookup->{'table'},
                    $lookup->{'key'}, $ref->[$i]
                );
                ##report_error($sql_string);
                my $sth = $d->prepare($sql_string)
                  || report_error(
                    format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
                $sth->execute()
                  || report_error(
                    format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
                my $lookup_ref = $sth->fetchrow_hashref();
                if ( ref( $lookup->{'label'} ) eq 'ARRAY' ) {
                    my @label_array = ();
                    foreach ( @{ $lookup->{'label'} } ) {
                        push( @label_array, $lookup_ref->{$_} );
                    }
                    $self->{'fields'}->[$i]->{'field_value'} =
                      join( ' ', @label_array );

                }
                else {
                    $self->{'fields'}->[$i]->{'field_value'} =
                      $lookup_ref->{ $lookup->{'label'} };
                }
            }
            else {
                $field->{'field_value'} = $ref->[$i];
                if ( $field->{'multiple'} ) {
                    foreach
                      my $field_value ( split( ',', $field->{'field_value'} ) )
                    {
                        $field_values{$field_value} = 1;
                    }
                }
                foreach my $option ( @{ $field->{'options'} } ) {
                    if ( $field->{link} ) {
                        ## link fields have to match on value, not name
                        if ( $option->{value} eq $ref->[$i] ) {
                            $option->{selected} = 'selected';
                        }

                    }
                    else {
                        if (   $option->{name} eq $ref->[$i]
                            || $field_values{ $option->{name} } )
                        {
                            $option->{selected} = 'selected';
                        }
                    }
                }
            }
        } ## end not key field
    }
    if ( $c->param('debug') ) {
        printf( "<pre>%s</pre>", Dumper( $self->{'external_fields'} ) );
    }

}

sub initialise_fields {
    my $self = shift();

    ##my $count = 0;

    # External fields first
    if ( $self->{links} ) {
        for ( my $i = 0 ; $i < @{ $self->{links} } ; $i++ ) {
            my $rel = @{ $self->{links} }[$i];

            $self->{'external_fields'}->[$i]->{field_name} =
              $rel->{'field_name'};


## wrap an if-clause around this for "if we have a multiple_select_field param"
## if we do, then we need to select that third value too, display it in a select field,
## and lose the checkbox

            my $list_sql_string = sprintf(
                "SELECT %s,%s as dauphin_id FROM `%s`",
                join( ',',
                    map { "`$_`" }
                      @{ $rel->{external_table}->{'label_fields'} } ),
                      "`$rel->{external_table}->{key_field}`",
                $rel->{external_table}->{name}
            );
            

            
            
            if( $rel->{external_table}->{sort_field}){
             $list_sql_string .= sprintf(" ORDER BY `%s`", $rel->{external_table}->{sort_field});
            }

            my $lister = $d->prepare($list_sql_string)
              || report_error(
                format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
            $lister->execute()
              || report_error(
                format_error( $lister->errstr(), __PACKAGE__, __LINE__ ) );
                
            while ( my ( $value, $id ) = $lister->fetchrow_array() ) {
                push(
                    @{ $self->{'external_fields'}->[$i]->{'values'} },
                    {
                        value      => $value,
                        field_name => $rel->{'field_name'},
                        dauphin_id => $id
                    }
                );
            }
            if ( $rel->{type} eq 'multiple' ) {
                $self->{'external_fields'}->[$i]->{multiple} = 1;
            }
            ## $count++;
        }
    }

    # All normal fields
    ############################################################################
    #    Looks up all possible fields by doing a "show full columns" to get    #
    #    the current fields/field options of the database.                     #
    ############################################################################
    my $sth =
      $d->prepare(
        "show full columns from " . '`' . $self->{'main_table'} . '`' )
      || report_error( format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
    $sth->execute()
      || report_error( format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
    $self->{'fields'} = [];
    ############################################################################
    #    For each field, get their name, whether they're the magic fields      #
    #    or not, and grab the correct display format from field_format, then   #
    #    push them into the fields array.                                      #
    ############################################################################

    while ( my $ref = $sth->fetchrow_hashref() ) {

        ## if defined as a lookup, something different

        if ( $self->{'lookups'}->{ $ref->{'Field'} } ) {
            if ( $mode eq 'delete' || $mode eq 'display' )
            {    ## no need to fetch options
                my %row = (
                    field_name => $ref->{'Field'},
                    lookup     => 1,
                    comment    => $ref->{'Comment'}
                );
                push( @{ $self->{'fields'} }, \%row );
            }
            else {
                my $lookup = $self->{'lookups'}->{ $ref->{'Field'} };
                my %row    = ( select => 1, link => 1 );

                my $lookup_string = '';
                if ( ref( $lookup->{'label'} ) eq 'ARRAY' ) {
                    $lookup_string =
                      join( ',', map { "`$_`" } @{ $lookup->{'label'} } );
                }
                else {
                    $lookup_string = $lookup->{'label'};
                }

                my $sql_string = sprintf( "SELECT `%s`,`%s` from `%s`",
                    $lookup->{'key'}, $lookup_string, $lookup->{'table'} );
                ##report_error($lookup_string);
                my $sth = $d->prepare($sql_string)
                  || report_error(
                    format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
                $sth->execute()
                  || report_error(
                    format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );

                my @options = ();
                while ( my $ref = $sth->fetchrow_hashref() ) {
                    my $label_string;
                    if ( ref( $lookup->{'label'} ) eq 'ARRAY' ) {
                        $label_string =
                          join( ', ', @$ref{ @{ $lookup->{'label'} } } );
                    }
                    else {
                        $label_string = $ref->{ $lookup->{'label'} };
                    }
                    push(
                        @options,
                        {
                            name  => $label_string,
                            value => $ref->{ $lookup->{'key'} }
                        }
                    );
                }

                $row{'field_name'} = $ref->{'Field'};
                $row{'comment'}    = $ref->{'Comment'};
                $row{'options'}    = \@options;

                push( @{ $self->{'fields'} }, \%row );
            }
        }
        else {
            my %row = field_format( $ref->{'Type'} );
            $row{'field_name'} = $ref->{'Field'};
            $row{'comment'}    = $ref->{'Comment'};
            if ( $ref->{'Field'} eq $self->{'key_field'} ) {
                $row{'key_field'} = 1;
            }
            if ( $ref->{'Field'} eq $self->{'title_field'} ) {
                $row{'title_field'} = 1;
            }
            push( @{ $self->{'fields'} }, \%row );
        }
    }
}

sub update_record {
    my $self = shift();
    ## handle all the external relationships:
    ## clean house first
    foreach my $rel ( @{ $self->{links} } ) {
        my $delete_string = sprintf(
            "DELETE FROM %s WHERE %s  = ?",
            $rel->{joining_table}->{name},
            $rel->{joining_table}->{left_column}
        );
        my $sth = $d->prepare($delete_string)
          || report_error(
            format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
        $sth->execute( $c->param('id') )
          || report_error(
            format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );

        # joining table is clean; insert/re-insert values:
        my $insert_string = sprintf(
            "INSERT INTO %s (%s,%s) values(?,?)",
            $rel->{joining_table}->{name},
            $rel->{joining_table}->{left_column},
            $rel->{joining_table}->{right_column}
        );
        $sth = $d->prepare($insert_string)
          || report_error(
            format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
        my @insert_values = $c->param( $rel->{field_name} );

        foreach (@insert_values) {
            $sth->execute( $c->param('id'), $_ )
              || report_error(
                format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
        }
    }
    $self->{'values'}      = ();
    $self->{'field_names'} = ();
    foreach my $field ( @{ $self->{'fields'} } ) {
        ########################################################################
        #  If the field can have multiple values, then we need to handle       #
        #  it differently -- join with commas all the values which CGI has     #
        #  put into an array for us                                            #
        ########################################################################
        if ( $field->{'multiple'} ) {
            push(
                @{ $self->{'values'} },
                join( ',', $c->param( $field->{field_name} ) )
            );
        }
        else {
            #####################################################################
           #  Else just push the single value into the values array            #
            #####################################################################
            unless ( $field->{'key_field'} ) {
                push(
                    @{ $self->{'values'} },
                    $c->param( $field->{field_name} )
                );
            }
        }
        #####################################################################
        #  push a quoted field name into a temp array of field names        #
        #  just used for the insert statement.                              #
        #####################################################################
        unless ( $field->{'key_field'} ) {
            push( @{ $self->{'field_names'} }, "`$field->{field_name}` = ?" );
        }
    }
    ########################################################################
    #  Another temporary string just used to build the SQL query for       #
    #  the update                                                          #
    ########################################################################
    my $field_name_string = join( ', ', @{ $self->{'field_names'} } );
    ########################################################################
    #  Prepare the statement:                                              #
    ########################################################################
    my $update_string = sprintf( "UPDATE `%s` SET %s WHERE %s = ?",
        $self->{main_table}, $field_name_string, $self->{'key_field'} );

    ## report_error( $update_string . '; '. join('\\',@{ $self->{'values'} }  ));

    my $sth = $d->prepare($update_string)
      || report_error( format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );

    $sth->execute( @{ $self->{'values'} }, $c->param('id') )
      || report_error( format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
    $self->output_list(
        message => 'record saved',
        offset  => $c->param('offset'),
        string  => $c->param('string')
    );
}

sub insert_record {
    my $self               = shift();
    my @values             = ();
    my @value_placeholders = ();
    foreach my $field ( @{ $self->{'fields'} } ) {
        ########################################################################
        #  unless it's the field we've specified as the key, normally the      #
        #  ID field, in which we put in a question mark because it's auto-inc  #
        ########################################################################
        if ( $field->{key_field} ) {
            push( @values, '?' );
        }
        #####################################################################
        #  If the field can have multiple values, then we need to           #
        #  handle it differently -- join with commas all the values         #
        #  which CGI has put into an array for us                           #
        #####################################################################
        elsif ( $field->{'multiple'} ) {
            my @field_values = $c->param( $field->{field_name} );
            push( @values, join( ',', @field_values ) );
        }
        else {
            push( @values, $c->param( $field->{field_name} ) );
        }
        ##################################################################
        #  Put another question mark into the array.                     #
        ##################################################################
        push( @value_placeholders, '?' );
    }
    my $placeholder_list = join( ', ', @value_placeholders );
    ########################################################################
    #  Prepare the statement:                                              #
    ########################################################################
    my $sql_string = sprintf( "INSERT INTO `%s` VALUES(%s)",
        $self->{main_table}, $placeholder_list );

    my $sth = $d->prepare($sql_string)
      || report_error( format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );

    $sth->execute(@values)
      || report_error( format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
    ######################################################################
    #  Grab the last insert ID,                                          #
    ######################################################################
    my $id = $d->last_insert_id( undef, undef, $self->{main_table}, undef );
    foreach my $rel ( @{ $self->{links} } ) {

        # joining table is clean; insert/re-insert values:

        my $insert_string = sprintf(
            "INSERT INTO %s (%s,%s) VALUES(?,?)",
            $rel->{joining_table}->{name},
            $rel->{joining_table}->{left_column},
            $rel->{joining_table}->{right_column}
        );
        ##report_error($insert_string);
        $sth = $d->prepare($insert_string)
          || report_error(
            format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
        my @insert_values = $c->param( $rel->{field_name} );
        foreach (@insert_values) {
            $sth->execute( $id, $_ )
              || report_error(
                format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
        }
    }
    $self->output_list(
        message => 'new record inserted',
        offset  => $c->param('offset'),
        string  => $c->param('string')
    );
}

sub field_format {
    my %hash = ();
    my $type = shift();
    ########################################################################
    #  All these SQL field types come out as text fields                   #
    ########################################################################
    if (   $type =~ m/varchar\((\d+)\)/
        || $type =~ m/int/
        || $type =~ m/float/
        || $type =~ m/time/
        || $type =~ m/float/
        || $type =~ m/double/ )
    {
        %hash = ( text => 1, maxlength => $1 );
    }
    ########################################################################
    #  Dates get handled differently                                       #
    ########################################################################
    elsif ( $type =~ m/date/ ) {
        %hash = ( date => 1 );
    }
    ########################################################################
    #  Any field ending in "text" comes out as a textarea                  #
    ########################################################################
    elsif ( $type =~ m/text$/ ) {
        %hash = ( textarea => 1 );
    }
    ########################################################################
    #  SETs can be set to one or more values from a list, but ENUMs        #
    #  can only have one value                                             #
    ########################################################################
    elsif ( $type =~ m/^set\(([^)]+)\)/ ) {
        my $set_options = $1;
        my @options     = ();
        while ( $set_options =~ m/'([^']+)'/g ) {
            push( @options, { name => $1 } );
        }
        %hash = ( select => 1, multiple => 1, options => \@options );
    }
    elsif ( $type =~ m/^enum\(([^)]+)\)/ ) {
        my $set_options = $1;
        my @options     = ();
        while ( $set_options =~ m/'([^']+)'/g ) {
            push( @options, { name => $1 } );
        }
        %hash = ( select => 1, options => \@options );
    }
    else {
        %hash = ( textarea => 1 );    ## hope for the best...
    }
    return %hash;
}
my $error_template = q(
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8">
	<link rel="stylesheet" href="css/dauphin.css">
	<title>error - dauphin</title>
</head>
<body>
	<h1>error - dauphin</h1>
	<h2>The following errors occurred</h2>
	<tmpl_loop name="errors">
	<ul>
	<li>
		<tmpl_var name="message">
	</li>
	</ul>
	</tmpl_loop>
</body>
</html>);

sub report_error {
    my @errors = ();
    push( @errors, { message => $_ } ) foreach @_;
    my $template = HTML::Template->new(
        scalarref         => \$error_template,
        die_on_bad_params => 0,
    );
    $template->param( errors => \@errors );
    print $template->output();
    exit();
}

sub format_error {
    return sprintf( "%s (%s, line %s)", @_ );
}

sub get_lookup_value {
    my ( $self, $lookup_name, $value ) = @_;
   ##print "<h1>\@_: $self, $lookup_name, $value</h1>";
    my $lookup = $self->{'lookups'}->{$lookup_name};
    my $lookup_string;
    if ( ref( $lookup->{'label'} ) eq 'ARRAY' ) {
        $lookup_string = join( ',', map { "`$_`" } @{ $lookup->{'label'} } );
    }
    else {
        $lookup_string = "`$lookup->{'label'}`";
    }

    my $sql_string = sprintf( "SELECT %s from `%s` where `%s` = %s",
        $lookup_string, $lookup->{'table'}, $lookup->{'key'}, $value );

    my $sth = $d->prepare($sql_string)
      || report_error( format_error( $d->errstr(), __PACKAGE__, __LINE__ ) );
    $sth->execute()
      || report_error( format_error( $sth->errstr(), __PACKAGE__, __LINE__ ) );
    my $lookup_ref = $sth->fetchrow_hashref();
    if ( ref( $lookup->{'label'} ) eq 'ARRAY' ) {
        return join( ' ', @$lookup_ref{ @{ $lookup->{'label'} } } );
    }
    else {
        return $lookup_ref->{ $lookup->{'label'} };
    }
}
###########################################################################
#  Return true value.                                                     #
###########################################################################
1;


